/**
 * Created by 赵波 on 2021/6/11.
 * 文章路由
 */
var mysql = require('mysql');
var config = require('../config/default.js');

var pool = mysql.createPool({
    host: config.database.HOST,
    user: config.database.USERNAME,
    password: config.database.PASSWORD,
    database: config.database.DATABASE
});
let query = function(sql, values) {
    return new Promise((resolve, reject) => {
        pool.getConnection(function(err, connection) {
            if (err) {
                resolve(err);
            } else {
                connection.query(sql, values, (err, rows) => {
                    if (err) {
                        reject(err);
                    } else {
                        resolve(rows);
                    }
                    connection.release();
                })
            }
        })
    })
};
// 用户表
let users =
    `create table if not exists users(
     id INT NOT NULL AUTO_INCREMENT,
     name VARCHAR(100) NOT NULL,
     pass VARCHAR(40) NOT NULL,
     PRIMARY KEY ( id )
    );`;
// 文章表
let posts =
    `create table if not exists posts(
     id INT NOT NULL AUTO_INCREMENT,
     name VARCHAR(100) NOT NULL,
     title VARCHAR(40) NOT NULL,
     content  VARCHAR(255) NOT NULL,
     uid  VARCHAR(40) NOT NULL,
     moment  VARCHAR(40) NOT NULL,
     comments  VARCHAR(40) NOT NULL DEFAULT '0',
     pv  VARCHAR(40) NOT NULL DEFAULT '0',
     PRIMARY KEY ( id )
    );`;
// 评论表
let comment =
    `create table if not exists comment(
     id INT NOT NULL AUTO_INCREMENT,
     name VARCHAR(100) NOT NULL,
     content VARCHAR(255) NOT NULL,
     postid VARCHAR(40) NOT NULL,
     PRIMARY KEY ( id )
    );`;
// 悬赏贴
let xsposts = `
create table if not exists xsposts(
    id INT NOT NULL AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    title VARCHAR(40) NOT NULL,
    content  VARCHAR(255) NOT NULL,
    uid  VARCHAR(40) NOT NULL,
    moment  VARCHAR(40) NOT NULL,
    comments  VARCHAR(40) NOT NULL DEFAULT '0',
    pv  VARCHAR(40) NOT NULL DEFAULT '0',
    isaccept varchar(10) default '否',
    money varchar(10),
    PRIMARY KEY ( id )
   );
`
    // 接取悬赏表
let acceptxs = `create table if not exists acceptxs(
    id INT NOT NULL AUTO_INCREMENT,
    acceptname VARCHAR(100) NOT NULL,
    phonenum VARCHAR(40) NOT NULL,
    uid VARCHAR(40) NOT NULL,
    xspostsid VARCHAR(40) NOT NULL,
    PRIMARY KEY ( id )
   );`
let createTable = function(sql) {
    return query(sql, []);
};
//建表
createTable(users);
createTable(posts);
createTable(comment);
createTable(xsposts)

createTable(acceptxs)

// 根据订单id查看自己悬赏被谁接取了
let findWhoxsPost = function(value) {
    let sql = `select *from acceptxs where xspostsid=?`
    return query(sql, value)
}

// 根据自己的用户id查看自己接了哪些悬赏

let findmyxsPost = function(value) {
    let sql = `select *from acceptxs where uid=?`
    return query(sql, value)
}
let updateUser = function(value) {
    let sql = `update users set name=?,pass=? where id=?`
    return query(sql, value)
}
let selectDataById = function(value) {
        let sql = `select * from acceptxs where xspostsid=?`
        return query(sql, value)
    }
    // 发表悬赏贴
let insertxsPosts = function(value) {
        let sql = `insert into xsposts(name,title,content,uid,moment,money) values(?,?,?,?,?,?)`
        return query(sql, value)
    }
    // 查看所有的悬赏贴
let findAllxs = function() {
        let sql = `select *from xsposts;`
        return query(sql)
    }
    //查找所有悬赏贴
let findAllxsPosts = function() {
    let sql = "select * from xsposts where isaccept='否';";
    return query(sql);
};
// 查找用户的悬赏贴
let findxsDataByUser = function(value) {
        let sql = "select * from xsposts where isaccept='否' and name=?;";
        return query(sql, value)
    }
    // 查看用户的所有的悬赏贴（包括未被接取的和已经接取了的）
let findxsAllDataByUser = function(value) {
    let sql = "select * from xsposts where name=?;";
    return query(sql, value)
}

// 根据id来查找对应的悬赏贴
let findxsDataById = function(id) {
    let sql = 'select * from xsposts where id=?;';
    return query(sql, id);
};
//更新悬赏浏览数
let updatexsPostPv = function(value) {
    let sql = "update xsposts set pv=? where id=?;";
    return query(sql, value);
};
// 接取悬赏
let insertAccept = function(value) {
    let sql = "insert into acceptxs(acceptname,phonenum,uid,xspostsid) values(?,?,?,?);";
    return query(sql, value);
};
//更新悬赏帖子状态
let updateAcceptPost = function(value) {
    let sql = "update xsposts set isaccept='是' where id=?;";
    return query(sql, value);
};
//注册用户
let insertData = function(values) {
    let sql = "insert into users(name,pass) values(?,?);";
    return query(sql, values);
};
//发表文章
let insertPost = function(value) {
    let sql = "insert into posts(name,title,content,uid,moment) values(?,?,?,?,?);";
    return query(sql, value);
};
//更新文章评论数
let updatePostComment = function(value) {
    let sql = "update posts set comments=? where id=?;";
    return query(sql, value);
};
//根据名字查找用户
let findDataByName = function(name) {
    let sql = 'SELECT * from users where name=?;';
    return query(sql, name);
};
//根据用户查找文章
let findDataByUser = function(name) {
    let sql = 'select * from posts where name=?;';
    return query(sql, name);
};
//查找所有文章
let findAllPost = function() {
    let sql = "select * from posts;";
    return query(sql);
};
//根据文章Id查找
let findDataById = function(id) {
    let sql = 'select * from posts where id=?;';
    return query(sql, id);
};
//更新文章浏览数
let updatePostPv = function(value) {
    let sql = "update posts set pv=? where id=?;";
    return query(sql, value);
};
//根据文章id查找所有评论
let findCommentById = function(id) {
    let sql = 'select * from comment where postid=?;';
    return query(sql, id);
};
//发表评论
let insertComment = function(value) {
    let sql = "insert into comment(name,content,postid) values(?,?,?);";
    return query(sql, value);
};
//删除评论
let deleteComment = function(commentid) {
    let sql = 'delete from comment where id=?;';
    return query(sql, commentid);
};
//删除所有文章评论
let deleteAllPostComment = function(postid) {
    let sql = 'delete from comment where postid=?;';
    return query(sql, postid);
};
//删除文章
let deletePost = function(postid) {
    let sql = 'delete from posts where id=?;';
    return query(sql, postid);
};
// 删除悬赏
let deletexspost = function(postid) {
        let sql = `delete from xsposts where id=?`
        return query(sql, postid)

    }
    //修改文章
let updatePost = function(value) {
    let sql = 'update posts set title=?,content=? where id=?;';
    return query(sql, value);
};
let updatexsPost = function(value) {
        let sql = 'update xsposts set title=?,content=?,money=? where id=?;';
        return query(sql, value);
    }
    //查找评论数
let fingCommentCount = function(postid) {
    let sql = 'select count(1) as num from comment where postid=?;';
    return query(sql, postid);
};
// 删除所有的用户
let deleteuser = function(postid) {
    let sql = `delete from users where id=?`
    return query(sql, postid)
}
let findAllUser = function() {
    let sql = `select *from users`
    return query(sql)
}
module.exports = {
    updateUser,
    selectDataById,
    query,
    createTable,
    insertData,
    insertPost,
    updatePostComment,
    findDataByName,
    findDataByUser,
    findAllPost,
    findDataById,
    updatePostPv,
    updatexsPostPv,
    findCommentById,
    insertComment,
    deleteComment,
    deleteAllPostComment,
    deletePost,
    updatePost,
    fingCommentCount,
    insertxsPosts,
    findAllxsPosts,
    findxsDataByUser,
    findxsDataById,
    insertAccept,
    updateAcceptPost,
    findxsAllDataByUser,
    updatexsPost,
    findmyxsPost,
    findWhoxsPost,
    findAllxs,
    deletexspost,
    deleteuser,
    findAllUser
};